package com.face.pass.active.dao;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.face.pass.active.model.Machine;

@Repository
public interface MachineDao extends JpaRepository<Machine, Long> {
	
	Machine findByMachineId(String machineId);
	
	@Query(value="SELECT CONVERT(FORMATDATETIME(active_date,'M'),INT) as `month`,count(1) as activeMum from machine WHERE active=:active and active_date between :startDate and :endDate GROUP BY `month` ORDER BY `month` asc",nativeQuery=true)
	List<Object[]> countAllActive(@Param("active") Integer active,@Param("startDate")Date startDate,@Param("endDate")Date endDate);
}
